Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports Npgsql

Namespace Persistencia.ModuloPlanificacion

    Public Class bd

#Region "Atributos"

        Private Shared instance As bd
        Private conn As NpgsqlConnection
        Private stringConnection As String = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=golden;Database=bdmsh;"

#End Region

        'Constructor
        Public Sub New()
            Try
                conn = New NpgsqlConnection(stringConnection)
            Catch ex As NpgsqlException
                MsgBox(ex)
            End Try
        End Sub

#Region "getInstance"
        'Patron Singleton 
        Public Shared Function getInstance() As bd

            If instance Is Nothing Then
                instance = New bd
            End If

            Return instance

        End Function
#End Region

#Region "funciones"

        Private Sub Open()
            conn.Open()
        End Sub

        Private Sub Close()
            conn.Close()
        End Sub

        Public Function EjecutarConRetorno(ByVal query As String) As Hashtable
            Dim array As New Hashtable
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()

                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()

                Dim i As Integer
                While dr.Read()
                    For i = 0 To dr.FieldCount - 1
                        Dim dato As String = CStr(dr(dr.GetName(i).ToString))
                        Dim nombrecampo As String = dr.GetName(i).ToString
                        array.Add(nombrecampo, dato)
                    Next i
                End While

                Me.Close()

                Return array
            Catch ex As Exception
                Me.Close()
                Throw ex
            End Try
            Return Nothing

        End Function

        Public Sub EjecutarSinRetorno(ByVal query As String)
            Try
                Me.Open()
                Dim command As New NpgsqlCommand(query, conn)
                command.ExecuteNonQuery()
                Me.Close()
            Catch ex As Exception
                Me.Close()
                Throw ex
            End Try

        End Sub

        Public Function ObtenerGrupo(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As GrupoUsuario
                While dr.Read()
                    Objeto = New GrupoUsuario(CStr(dr("id")), CStr(dr("nombregrupo")), CStr(dr("accesoadministracion")), CStr(dr("accesoinformes")), CStr(dr("accesovistapanol")), CStr(dr("accesobodega")), _
                                            CStr(dr("accesoindicadores")), CStr(dr("accesobusqueda")), CStr(dr("accesorecursoshumanos")), CStr(dr("accesoplanificacion")), CStr(dr("accesocontabilidad")), _
                                            CStr(dr("accesosistema")), CStr(dr("accesosistemageneral")), CStr(dr("accesosolicitudbodega")), CStr(dr("accesosistemapersonal")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()
                Throw ex
            End Try
            Return Nothing

        End Function

        Public Function ObtenerOt(ByVal query As String) As ArrayList

            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As OrdenTrabajo
                While dr.Read()
                    Objeto = New OrdenTrabajo( _
                    CStr(dr("descripcion")), _
                    CStr(dr("fkpresupuesto")), _
                    CStr(dr("estado")), _
                    CStr(dr("fechatermino")), _
                    CStr(dr("requisicion")), _
                    CStr(dr("ordencompra")), _
                    CStr(dr("taller")), _
                    CStr(dr("creado_el")), _
                    CStr(dr("creado_por")), _
                    CStr(dr("id")))
                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            Return Nothing

        End Function

        Public Function ObtenerCriterioConsulta(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As ListItem
                While dr.Read()
                    Objeto = New ListItem
                    Objeto.Value = CStr(dr("dato"))
                    Objeto.Text = CStr(dr("nombre"))
                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            Return Nothing
        End Function

        Public Function ObtenerPlanificacion(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Planificacion
                While dr.Read()
                    Objeto = New Planificacion( _
                        CInt(dr("ot_fk")), _
                        CStr(dr("fecha")), _
                        CInt(dr("id")), _
                        CInt(dr("k")))

                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()

            End Try
            Return Nothing
        End Function

        Public Function ObtenerPlanificacionM1(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Planificacion_M1
                While dr.Read()
                    Objeto = New Planificacion_M1( _
                        CStr(dr("marca")), _
                        CStr(dr("cantidad")), _
                        CStr(dr("descripcion")), _
                        CInt(dr("kguni")), _
                        CStr(dr("observacion")), _
                        CStr(dr("plano")), _
                        CBool(dr("planificado")), _
                        CStr(dr("porcentaje_avance")), _
                        CInt(dr("fk_planificacion")), _
                        CInt(dr("id")))

                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerPlanificacionM2(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Planificacion_M2
                While dr.Read()
                    Objeto = New Planificacion_M2( _
                        CStr(dr("marca")), _
                        CStr(dr("descripcion")), _
                        CInt(dr("cantidad")), _
                        CInt(dr("largo")), _
                        CStr(dr("ancho")), _
                        CStr(dr("observacion")), _
                        CInt(dr("pk_plani_m1")), _
                        CInt(dr("id")))

                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Throw ex
                Me.Close()
            End Try
            Return Nothing
        End Function

        Public Function ObtenerPlanificacionM3(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Planificacion_M3
                While dr.Read()
                    Objeto = New Planificacion_M3( _
                        CStr(dr("estacion")), _
                        CStr(dr("tiempo")), _
                        CStr(dr("observacion")), _
                        CInt(dr("fk_plani_m2")), _
                        CInt(dr("id")), _
                        CInt(dr("total")))
                   
                  
                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()
            End Try
            Return Nothing
        End Function

        Public Function ObtenerEstaciones(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Estacion
                While dr.Read()
                    Objeto = New Estacion( _
                        CStr(dr("estacion")), _
                        CStr(dr("descripcion")), _
                        CInt(dr("id")))

                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()
            End Try
            Return Nothing
        End Function

        Public Function ObtenerBibliotecaTecnica(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Biblioteca_Tecnica
                While dr.Read()
                    Objeto = New Biblioteca_Tecnica( _
                        CStr(dr("nombre")), _
                        CDbl(dr("peso")), _
                        CStr(dr("descripcion")), _
                        CStr(dr("tipo")), _
                        CDbl(dr("superficie")), _
                        CInt(dr("id")))

                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()
            End Try
            Return Nothing
        End Function

        Public Function ObtenerPlanificacionProgramar(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Programar
                While dr.Read()
                    Objeto = New Programar( _
                        CInt(dr("fk_plan_m3")), _
                        CStr(dr("f_inicio")), _
                        CStr(dr("f_fin")), _
                        CStr(dr("f_planificado")), _
                        CInt(dr("cantidad_asignados")), _
                        CInt(dr("cantidad_terminados")), _
                        CInt(dr("id")))

                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()
            End Try
            Return Nothing
        End Function

        Public Function ObtenerPlanificacionProgramarSumaTotal(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Programar
                While dr.Read()
                    Objeto = New Programar( _
                        CInt(dr("fk_plan_m3")), _
                        -1, _
                        "", _
                        "", _
                        CInt(dr("cantidad_asignados")), _
                        CInt(dr("cantidad_terminados")), _
                        -1)

                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As Exception
                Me.Close()
            End Try
            Return Nothing
        End Function

#End Region

    End Class

End Namespace

